package com.wyx.jdbc.transaction;

import com.wyx.jdbc.utility.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author 王艺锡
 * @version 1.0
 * 事务
 */
public class transaction_ {
    //1.JDBC程序中当一个Connection对象创建时，默认情况下是自动提交事务
    //2.JDBC中为了让多个SQL语句作为一个整体执行，需要使用事务

    //3.调用Connection的setAutoCommit(false)可以取消自动提交事务
    //4.在所有的SQL语句执行后，调用Connection的commit()方法提交事务
    //5.在其中某个操作失败或者出现异常的时候，调用Connection的rollback()方法回滚事务



    //当没有使用事务时,执行语句自动提交
    @Test
    public void noTransaction() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql1 = "update ACCOUNT set balance = balance - 100 where id = ?";
        String sql2 = "update ACCOUNT set balance = balance + 100 where id = ?";

        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, 1);
            preparedStatement.executeUpdate();

            int i = 1 / 0;
            preparedStatement.setInt(1, 2);
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }


    }


    //使用事务
    @Test
    public void useTransaction(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    String sql1 = "update ACCOUNT set balance = balance - 100 where id = 1";
    String sql2 = "update ACCOUNT set balance = balance + 100 where id = 2";

        try {
            connection = JDBCUtils.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            //int i = 1/0;
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (Exception e) {
            System.out.println("捕获到异常，回滚..");
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }


    }



}
